﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Service.DAL
{
    class RingtoneDAL
    {
        DataAccessHelper db;
        public RingtoneDAL()
        {
            db = new DataAccessHelper();
        }
        public SqlDataReader GetNewRingtone(Category category, int number)
        {
            SqlDataReader reader = db.ExecuteReader("Select Top(" + number + ") Cate_Ring.Id, Ring.Code, Ring.Title, Ring.Artist, Cate.Category, Ring.Url, Ring.Download, Ring.Size From tblCategory Cate, tblRingtone Ring, tblCategory_Ringtone Cate_Ring Where Cate.CateId = Cate_Ring.CategoryId and Ring.Code = Cate_Ring.RingtoneCode and Cate.CateId = '" + (int)category + "' Order by Cate_Ring.Id desc");
            return reader;
        }

        public long GetNewId(Category category)
        {
            long total = (long)db.ExecuteScalar("Select MAX(Id) From tblCategory_Ringtone Where CategoryId = '" + (int)category + "'");
            return total + 1;
        }

        public long GetMaxId()
        {
            long total = (long)db.ExecuteScalar("Select MAX(Id) From tblCategory_Ringtone");
            return total + 1;
        }

        public long GetLastId(Category category)
        {
            return (long)db.ExecuteScalar("Select MIN(Id) From tblCategory_Ringtone Where CategoryId = '" + (int)category + "'");
        }

        public SqlDataReader GetNextRingtone(Category category, long currentId, int number)
        {
            SqlDataReader reader = db.ExecuteReader("Select Top(" + number + ") Cate_Ring.Id, Ring.Code, Ring.Title, Ring.Artist, Cate.Category, Ring.Url, Ring.Download, Ring.Size From tblCategory Cate, tblRingtone Ring, tblCategory_Ringtone Cate_Ring Where Cate.CateId = Cate_Ring.CategoryId and Ring.Code = Cate_Ring.RingtoneCode and Cate.CateId = '" + (int)category + "' and Cate_Ring.Id < " + currentId + " Order by Cate_Ring.Id desc");
            return reader;
        }

        public SqlDataReader Search(string keyword,long currentId, int number)
        {
            SqlDataReader reader = db.ExecuteReader("Select Top(" + number + ") Cate_Ring.Id, Ring.Code, Ring.Title, Ring.Artist, Cate.Category, Ring.Url, Ring.Download, Ring.Size From tblCategory Cate, tblRingtone Ring, tblCategory_Ringtone Cate_Ring Where Cate.CateId = Cate_Ring.CategoryId and Ring.Code = Cate_Ring.RingtoneCode and Cate_Ring.Id < " + currentId + " and (Artist LIKE '%" + keyword + "%' or Title LIKE '%" + keyword + "%')  Order by Cate_Ring.Id desc");
            return reader;
        }

        public bool IncreaseDownload(long code)
        {
            long download = GetDownload(code);
            download++;
            int count = db.ExecuteNonQuery("Update tblRingtone Set Download = '" + download + "' Where Code = '" + code + "'");
            return count > 0;
        }

        public long GetDownload(long code)
        {
            return (long)db.ExecuteScalar("Select Download From tblRingtone Where Code = '" + code + "'");
        }
    }
}
